Databases are used to store information for an innumerable number of applications, including various commercial, industrial, technical, scientific and educational applications. As the reliance on information increases, both the volume of information stored in most databases, as well as the number of users wishing to access that information, likewise increases. Moreover, as the volume of information in a database, and the number of users wishing to access the database, increases, the amount of computing resources required to manage such a database increases as well.
Database management systems (DBMS's), which are the computer programs that are used to access the information stored in databases, therefore often require tremendous resources to handle the heavy workloads placed on such systems. As such, significant resources have been devoted to increasing the performance of database management systems with respect to processing searches, or queries, to databases.
Improvements to both computer hardware and software have improved the capacities of conventional database management systems. For example, in the hardware realm, increases in microprocessor performance, coupled with improved memory management systems, have improved the number of queries that a particular microprocessor can perform in a given unit of time. Furthermore, the use of multiple microprocessors and/or multiple networked computers has further increased the capacities of many database management systems.
From a software standpoint, the use of relational databases, which organize information into formally-defined tables consisting of rows and columns, and which are typically accessed using a standardized language such as Structured Query Language (SQL), has substantially improved processing efficiency, as well as substantially simplified the creation, organization, and extension of information within a database. Furthermore, significant development efforts have been directed toward query “optimization”, whereby the execution of particular searches, or queries, is optimized in an automated manner to minimize the amount of resources required to execute each query.
Through the incorporation of various hardware and software improvements, many high performance database management systems are able to handle hundreds or even thousands of queries each second, even on databases containing millions or billions of records. However, further increases in information volume and workload are inevitable, so continued advancements in database management systems are still required.
Even though current query optimizers are robust enough to routinely select the best access plan for execution, there are still instances in which query performance is below user expectations and other techniques are needed to improve performance of a database. A database monitor is a tool that automatically tracks and records information about a query as it executes. Current database monitors typically store this information in a database table with multiple records per query. This database table can itself be queried for records matching certain characteristics.
The type of data that can be collected by a database monitor is large and varied. Common statistics collected by a database monitoring tool include file size, memory size, processor speed, number of processors available, the SQL statement (with parameters replaced), type of SQL operation, number of row updated, inserted, or deleted, number of rows fetched, elapsed time for this operation, access plan rebuild code, table name, join fields, estimated completion time, estimate I/O operations, number of rows in the table, index name, hash used, host variables, join position, join method, join type, etc. In addition, other characteristics about the query can be collected as well.
The collected information can be analyzed by a database administrator to locate problematic queries, from a performance standpoint, who then can implement changes that may address the problems. Even though current database monitoring tools are powerful, improvements are needed that provide even more information to a database administrator. In particular, there are a variety of separate reasons why a complex query comprised of a number of instructions may take a long time to execute; however, current database monitoring tools do not provide enough details to determine why the query's performance may be below expectations. Thus, there remains the need in prior database environments for a system that permits tracking and collecting of performance statistics and information about individual elements within a query.